package com.vehiclemanagement.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.vehiclemanagement.entity.InsuranceRecord;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 车辆保险记录Mapper接口
 */
@Mapper
public interface InsuranceRecordMapper extends BaseMapper<InsuranceRecord> {

    /**
     * 自定义分页查询，包含关联的车辆信息
     *
     * @param page     分页参数
     * @param whereSql 自定义WHERE条件
     * @return 包含车辆信息的分页结果
     */
    @Select("SELECT ir.*, v.plate_number, " +
            "CASE ir.insurance_type " +
            "WHEN 1 THEN '交强险' " +
            "WHEN 2 THEN '商业险' " +
            "WHEN 3 THEN '车损险' " +
            "WHEN 4 THEN '第三者责任险' " +
            "ELSE '其他' END as insurance_type_name, " +
            "DATEDIFF(ir.end_time, NOW()) as remaining_days " +
            "FROM insurance_record ir " +
            "LEFT JOIN vehicle v ON ir.vehicle_id = v.id " +
            "WHERE ${whereSql} " +
            "ORDER BY ir.end_time ASC")
    IPage<InsuranceRecord> selectPageWithInfo(Page<InsuranceRecord> page, @Param("whereSql") String whereSql);

    /**
     * 计算指定车辆的总保费
     *
     * @param vehicleId 车辆ID
     * @return 总保费
     */
    @Select("SELECT SUM(premium) FROM insurance_record WHERE vehicle_id = #{vehicleId} AND del_flag = 0")
    BigDecimal sumPremiumByVehicleId(@Param("vehicleId") Long vehicleId);

    /**
     * 统计指定车辆的保险数量
     *
     * @param vehicleId 车辆ID
     * @return 保险数量
     */
    @Select("SELECT COUNT(*) FROM insurance_record WHERE vehicle_id = #{vehicleId} AND del_flag = 0")
    Integer countInsuranceByVehicleId(@Param("vehicleId") Long vehicleId);

    /**
     * 更新保险状态（自动根据当前时间和到期时间计算）
     */
    @Update("UPDATE insurance_record SET " +
            "status = CASE " +
            "WHEN end_time < NOW() THEN 3 " + // 已过期
            "WHEN DATEDIFF(end_time, NOW()) <= 30 THEN 2 " + // 即将到期(30天内)
            "ELSE 1 END, " + // 有效
            "update_time = NOW() " +
            "WHERE del_flag = 0")
    int updateInsuranceStatus();

    /**
     * 获取即将到期的保险记录
     *
     * @param days 天数，如获取30天内即将到期的保险
     * @return 即将到期的保险记录列表
     */
    @Select("SELECT ir.*, v.plate_number, " +
            "CASE ir.insurance_type " +
            "WHEN 1 THEN '交强险' " +
            "WHEN 2 THEN '商业险' " +
            "WHEN 3 THEN '车损险' " +
            "WHEN 4 THEN '第三者责任险' " +
            "ELSE '其他' END as insurance_type_name, " +
            "DATEDIFF(ir.end_time, NOW()) as remaining_days " +
            "FROM insurance_record ir " +
            "LEFT JOIN vehicle v ON ir.vehicle_id = v.id " +
            "WHERE ir.del_flag = 0 " +
            "AND ir.end_time > NOW() " +
            "AND DATEDIFF(ir.end_time, NOW()) <= #{days} " +
            "ORDER BY ir.end_time ASC")
    List<InsuranceRecord> selectExpiringInsurance(@Param("days") Integer days);

    /**
     * 获取已过期的保险记录
     *
     * @return 已过期的保险记录列表
     */
    @Select("SELECT ir.*, v.plate_number, " +
            "CASE ir.insurance_type " +
            "WHEN 1 THEN '交强险' " +
            "WHEN 2 THEN '商业险' " +
            "WHEN 3 THEN '车损险' " +
            "WHEN 4 THEN '第三者责任险' " +
            "ELSE '其他' END as insurance_type_name, " +
            "DATEDIFF(ir.end_time, NOW()) as remaining_days " +
            "FROM insurance_record ir " +
            "LEFT JOIN vehicle v ON ir.vehicle_id = v.id " +
            "WHERE ir.del_flag = 0 " +
            "AND ir.end_time < NOW() " +
            "ORDER BY ir.end_time DESC")
    List<InsuranceRecord> selectExpiredInsurance();

    /**
     * 按保险类型统计保费和保险金额
     *
     * @return 保险类型统计结果
     */
    @Select("SELECT insurance_type, " +
            "COUNT(*) AS insurance_count, " +
            "SUM(premium) AS total_premium, " +
            "SUM(insurance_amount) AS total_insurance_amount, " +
            "SUM(total_claim_amount) AS total_claim_amount " +
            "FROM insurance_record " +
            "WHERE del_flag = 0 " +
            "GROUP BY insurance_type " +
            "ORDER BY insurance_type")
    List<Map<String, Object>> statisticsByType();

    /**
     * 按年份统计保费和保险金额
     *
     * @return 年份统计结果
     */
    @Select("SELECT YEAR(start_time) AS year, " +
            "COUNT(*) AS insurance_count, " +
            "SUM(premium) AS total_premium, " +
            "SUM(insurance_amount) AS total_insurance_amount, " +
            "SUM(total_claim_amount) AS total_claim_amount " +
            "FROM insurance_record " +
            "WHERE del_flag = 0 " +
            "GROUP BY YEAR(start_time) " +
            "ORDER BY year")
    List<Map<String, Object>> statisticsByYear();

    /**
     * 增加理赔次数和金额
     *
     * @param id            保险记录ID
     * @param claimAmount   理赔金额
     * @return 影响的行数
     */
    @Update("UPDATE insurance_record SET " +
            "claim_count = claim_count + 1, " +
            "total_claim_amount = COALESCE(total_claim_amount, 0) + #{claimAmount}, " +
            "update_time = NOW() " +
            "WHERE id = #{id} AND del_flag = 0")
    int incrementClaimCount(@Param("id") Long id, @Param("claimAmount") BigDecimal claimAmount);

    /**
     * 物理删除记录
     *
     * @param id 记录ID
     * @return 影响的行数
     */
    @Delete("DELETE FROM insurance_record WHERE id = #{id}")
    int deletePhysically(@Param("id") Long id);
} 